﻿Imports MySql.Data.MySqlClient
Imports MySql.Data.Types
Imports System.Runtime.InteropServices
Imports System.Drawing
Imports System.Drawing.Imaging

Public Class classDB
    Public Event ErrorMsg(ByRef strErrorMsg As String)
    Public Event Progress(ByRef iPercentage As Integer)
    Public confMySQL_Hostname As String = ""
    Public confMySQL_Port As Integer = 3306
    Public confMySQL_Username As String = ""
    Public confMySQL_Password As String = ""
    Public confMySQL_Database As String = ""

    Public conn As New MySqlConnection


    Public Function Connect()
        Dim strConn As String = "Data Source=" & confMySQL_Hostname & ";" & _
                "User Id='" & confMySQL_Username & "';" & _
                "Password='" & confMySQL_Password & "';" &
                "Port=" & confMySQL_Port & ";" & _
                "Database='" & confMySQL_Database & "'"

        Try
            conn = New MySqlConnection(strConn)
            conn.Open()
        Catch ex As MySqlException
            Select Case ex.Number
                Case 0 : Debug.Print("DB", "Could not connect to server", "classDB", "Connect")
                Case 1045 : Debug.Print("DB", "Authentication Failed", "classDB", "Connect")
                Case 1146 : Debug.Print("DB", "Table dosnt exist", "classDB", "Connect")
                Case Else : Debug.Print("DB", ex.Message, "classDB", "Connect")
            End Select
        Catch ex As Exception
            Debug.Print("DB", ex.Message, "classDB", "Connect")
        End Try
        Return True
    End Function
    Public Function UpdateCollections(ByRef col As Collection) As Boolean
        Dim reader As MySqlDataReader = Nothing
        Dim ret As Boolean = False
        Try


            RaiseEvent Progress(0)
            'Hent ut timer
            Dim cmd As New MySqlCommand
            cmd.CommandText = "SELECT * FROM Trades ORDER BY updated DESC LIMIT " & My.Settings.db_DownloadRecords
            ' mysql_query("SELECT * FROM notes WHERE note_author_id='$u_id' ORDER BY date_time DESC LIMIT 0, 10
            cmd.Connection = conn
            reader = cmd.ExecuteReader
            RaiseEvent Progress(0)
            Dim prog As Integer = 0
            Dim rat As Double = 99 / reader.RecordsAffected
            While reader.Read
                Dim clsTrade As New classTrades
                With clsTrade
                    .ID = reader.GetInt32("ID")
                    .Symbol = reader.GetString("Symbol")
                    .Direction = reader.GetString("Direction")
                    .Open = reader.GetDateTime("Open")
                    .Close = reader.GetDateTime("Close")
                    .TP = reader.GetDouble("TP")
                    .SL = reader.GetDouble("SL")
                    .Comment = reader.GetString("Comment")
                    .priceEnter = reader.GetDouble("priceEnter")
                    .priceClose = reader.GetDouble("priceClose")
                    .Status = reader.GetString("Status")
                    .lotSize = reader.GetDouble("lotSize")
                    .System = reader.GetString("System")
                    'pic open
                    If Not (reader.IsDBNull(reader.GetOrdinal("picOpen"))) Then
                        Dim picSize As Integer = reader.GetInt32("picOpenSize")
                        Dim bData(picSize) As Byte
                        reader.GetBytes(reader.GetOrdinal("picOpen"), 0, bData, 0, picSize)
                        Dim iWidth As Integer = reader.GetInt32("picOpenWidth")
                        Dim iHeight As Integer = reader.GetInt32("picOpenHeight")
                        .picOpen = CreateBitmap(bData, iWidth, iHeight)
                    End If
                    'pic close
                    If Not (reader.IsDBNull(reader.GetOrdinal("picClose"))) Then
                        Dim picSize As Integer = reader.GetInt32("picCloseSize")
                        Dim bData(picSize) As Byte
                        reader.GetBytes(reader.GetOrdinal("picClose"), 0, bData, 0, picSize)
                        .picClose = CreateBitmap(bData, reader.GetInt32("picCloseWidth"), reader.GetInt32("picCloseHeight"))
                    End If
                End With
                If Not col.Contains(CStr(clsTrade.ID)) Then
                    col.Add(clsTrade, CStr(clsTrade.ID))
                End If
                prog = prog + rat
                'RaiseEvent Progress(prog)
            End While
            reader.Close()
            ret = True
            RaiseEvent Progress(100)

        Catch ex As MySqlException
            Select Case ex.Number
                Case 0 : Debug.Print("DB", "Could not connect to server", "classDB", "Connect")
                Case 1045 : Debug.Print("DB", "Authentication Failed", "classDB", "Connect")
                Case 1146 : Debug.Print("DB", "Table dosnt exist", "classDB", "Connect")
                Case Else : Debug.Print("DB", ex.Message, "classDB", "Connect")
            End Select
        Catch ex As Exception
            Debug.Print("DB", ex.Message, "classDB", "Connect")
        Finally
            If Not reader Is Nothing Then
                If Not reader.IsClosed Then reader.Close()
            End If
        End Try
        RaiseEvent Progress(100)
        Return ret
    End Function

    Public Function CreateBitmap(ByRef bData() As Byte, ByVal iWidth As Integer, ByVal iHeight As Integer) As Bitmap
        Dim ret As Bitmap = Nothing
        If iWidth = 0 Or iHeight = 0 Then Return Nothing
        Try
            Dim bmp As New Bitmap(iWidth, iHeight)
            Dim bmData As BitmapData = bmp.LockBits(New Rectangle(0, 0, iWidth, iHeight), ImageLockMode.WriteOnly, PixelFormat.Format24bppRgb)
            Marshal.Copy(bData, 0, bmData.Scan0, bData.Length)
            bmp.UnlockBits(bmData)
            Return bmp
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        Return ret
    End Function
    Public Function CreateBitmap(ByRef pic As Bitmap) As Byte()
        Try
            Dim bmData As BitmapData = pic.LockBits(New Rectangle(New Point(), pic.Size), ImageLockMode.ReadOnly, PixelFormat.Format24bppRgb)
            Dim byteCount As Integer = bmData.Stride * bmData.Height


            Dim ret(byteCount) As Byte
            Marshal.Copy(bmData.Scan0, ret, 0, byteCount)
            pic.UnlockBits(bmData)
            Return ret
        Catch ex As Exception
            MsgBox(ex.Message)
            Return Nothing
        End Try
    End Function


    Public Sub DeleteTrade(ByRef c As classTrades)
        Try
            Dim cmd As New MySqlCommand()
            cmd.Connection = conn
            cmd.CommandText = "DELETE FROM Trades WHERE ID = @id"
            cmd.Parameters.AddWithValue("@id", c.ID)
            cmd.ExecuteNonQuery()
        Catch ex As MySqlException
            Debug.Print("DB", ex.Message & " (" & ex.Number & ").", "classDB", "UpdateTimer")
        Catch ex As Exception
            Debug.Print("DB", ex.Message, "classDB", "UpdateTimer")
        End Try
    End Sub

    Public Function UpdateTrade(ByRef c As classTrades)
        Dim ret As Boolean = False
        Try
            Dim cmd As MySqlCommand
            If c.ID > 0 Then
                cmd = conn.CreateCommand
                cmd.CommandText = "DELETE FROM Trades WHERE ID = @id"
                cmd.Parameters.AddWithValue("@id", c.ID)
                cmd.ExecuteNonQuery()

                cmd = conn.CreateCommand


                cmd.CommandText = "INSERT INTO Trades ( " & _
                            "ID, Symbol, Direction, Open, Close, TP, SL, picOpen, picClose, Comment, priceEnter, priceClose, Status, picCloseSize, picOpenSize, updated, lotSize, System, picOpenWidth, picOpenHeight, picCloseWidth, picCloseHeight" & _
                            ") VALUES ( " & _
                            "@ID, @Symbol, @Direction, @Open, @Close, @TP, @SL, @picOpen, @picClose,@Comment, @priceEnter, @priceClose, @Status, @picCloseSize, @picOpenSize, now(), @lotSize, @System, @picOpenWidth, @picOpenHeight, @picCloseWidth, @picCloseHeight" & _
                            ")"
                cmd.Parameters.AddWithValue("@ID", c.ID)
            Else
                cmd = conn.CreateCommand


                cmd.CommandText = "INSERT INTO Trades ( " & _
                            "Symbol, Direction, Open, Close, TP, SL, picOpen, picClose, Comment, priceEnter, priceClose, Status, picCloseSize, picOpenSize, updated, lotSize, System, picOpenWidth, picOpenHeight, picCloseWidth, picCloseHeight" & _
                            ") VALUES ( " & _
                            "@Symbol, @Direction, @Open, @Close, @TP, @SL, @picOpen, @picClose,@Comment, @priceEnter, @priceClose, @Status, @picCloseSize, @picOpenSize, now(), @lotSize, @System, @picOpenWidth, @picOpenHeight, @picCloseWidth, @picCloseHeight" & _
                            ")"

            End If

            cmd.Parameters.AddWithValue("@Symbol", c.Symbol)
            cmd.Parameters.AddWithValue("@Direction", c.Direction)
            cmd.Parameters.AddWithValue("@Open", c.Open)
            cmd.Parameters.AddWithValue("@Close", c.Close)
            cmd.Parameters.AddWithValue("@TP", c.TP)
            cmd.Parameters.AddWithValue("@SL", c.SL)
            cmd.Parameters.AddWithValue("@Comment", c.Comment)
            cmd.Parameters.AddWithValue("@priceEnter", c.priceEnter)
            cmd.Parameters.AddWithValue("@priceClose", c.priceClose)
            cmd.Parameters.AddWithValue("@Status", c.Status)
            cmd.Parameters.AddWithValue("@lotSize", c.lotSize)
            cmd.Parameters.AddWithValue("@System", c.System)

            If c.picOpen Is Nothing Then
                cmd.Parameters.AddWithValue("@picOpen", Nothing)
                cmd.Parameters.AddWithValue("@picOpenSize", 0)
                cmd.Parameters.AddWithValue("@picOpenWidth", 0)
                cmd.Parameters.AddWithValue("@picOpenHeight", 0)
            Else
                Dim bData() As Byte = CreateBitmap(c.picOpen)
                cmd.Parameters.AddWithValue("@picOpen", bData)
                cmd.Parameters.AddWithValue("@picOpenSize", bData.Length)
                cmd.Parameters.AddWithValue("@picOpenWidth", c.picOpen.Width)
                cmd.Parameters.AddWithValue("@picOpenHeight", c.picOpen.Height)
            End If

            If c.picClose Is Nothing Then
                cmd.Parameters.AddWithValue("@picClose", Nothing)
                cmd.Parameters.AddWithValue("@picCloseSize", 0)
                cmd.Parameters.AddWithValue("@picCloseWidth", 0)
                cmd.Parameters.AddWithValue("@picCloseHeight", 0)
            Else
                cmd.Parameters.AddWithValue("@picClose", CreateBitmap(c.picClose))
                cmd.Parameters.AddWithValue("@picCloseSize", CreateBitmap(c.picClose).Length)
                cmd.Parameters.AddWithValue("@picCloseWidth", c.picOpen.Width)
                cmd.Parameters.AddWithValue("@picCloseHeight", c.picOpen.Height)
            End If

            cmd.ExecuteNonQuery()
            c.ID = cmd.LastInsertedId

            ret = True
        Catch ex As MySqlException
            Debug.Print("DB", ex.Message & " (" & ex.Number & ").", "classDB", "UpdateTimer")
            ret = False
        Catch ex As Exception
            Debug.Print("DB", ex.Message, "classDB", "UpdateTimer")
            ret = False
        End Try
        c.Online = ret
        Return ret
    End Function

    Public Function State() As System.Data.ConnectionState
        Return conn.State
    End Function

    Public Sub Close()
        If conn.State <> ConnectionState.Closed Then
            conn.Close()
        End If
    End Sub

    Protected Overrides Sub Finalize()
        If Not conn Is Nothing Then
            conn.Close()
        End If
        MyBase.Finalize()
    End Sub
End Class

Public Class classMySQLSettings
    Public Tables As New Collection
    Public TradesCollumns As New Collection

    'Dim CommandText = "CREATE  TABLE `Trades` (`ID` INT NOT NULL AUTO_INCREMENT , `Symbol` TEXT NOT NULL , `Direction` TEXT NOT NULL , `Open` DATETIME NOT NULL ," & _
    '                    "`Close` DATETIME NOT NULL ,`TP` DOUBLE NOT NULL , `SL` DOUBLE NOT NULL ,`picOpen` BINARY ,`picClose` BINARY ,`Comment` TEXT NOT NULL ," & _
    '                    "`priceEnter` DOUBLE NOT NULL ,`priceClose` DOUBLE NOT NULL , `Status` TEXT NOT NULL , `picOpenSize` INT NOT NULL , `picCloseSize` INT NOT NULL ," & _
    '                    "`updated` DATETIME NOT NULL , PRIMARY KEY (`ID`) )"

    Public Sub New()
        With Tables
            .Add(New classProperty("Trades", False, "CREATE  TABLE `Trades` (`ID` INT NOT NULL AUTO_INCREMENT , `Symbol` TEXT NOT NULL , `Direction` TEXT NOT NULL , `Open` DATETIME NOT NULL ," & _
                        "`Close` DATETIME NOT NULL ,`TP` DOUBLE NOT NULL , `SL` DOUBLE NOT NULL ,`picOpen` LONGBLOB ,`picClose` LONGBLOB ,`Comment` TEXT NOT NULL ," & _
                        "`priceEnter` DOUBLE NOT NULL ,`priceClose` DOUBLE NOT NULL , `Status` TEXT NOT NULL , `picOpenSize` INT NOT NULL , `picCloseSize` INT NOT NULL ," & _
                        "`updated` DATETIME NOT NULL , `lotSize` DOUBLE NOT NULL , `System` TEXT NOT NULL , `picOpenWidth` INT NOT NULL , `picOpenHeight` INT NOT NULL" & _
                        " , `picCloseWidth` INT NOT NULL , `picCloseHeight` INT NOT NULL, PRIMARY KEY (`ID`) )"), "Trades")
        End With
        With TradesCollumns
            .Add(New classProperty("ID", False, "INT NOT NULL AUTO_INCREMENT"), "ID")
            .Add(New classProperty("Symbol", False, "TEXT NOT NULL"), "Symbol")
            .Add(New classProperty("Direction", False, "TEXT NOT NULL"), "Direction")
            .Add(New classProperty("Open", False, "DATETIME NOT NULL"), "Open")
            .Add(New classProperty("Close", False, "DATETIME NOT NULL"), "Close")
            .Add(New classProperty("SL", False, "DOUBLE NOT NULL"), "SL")
            .Add(New classProperty("TP", False, "DOUBLE NOT NULL"), "TP")
            .Add(New classProperty("picOpen", False, "LONGBLOB"), "picOpen")
            .Add(New classProperty("picClose", False, "LONGBLOB"), "picClose")
            .Add(New classProperty("Comment", False, "TEXT NOT NULL"), "Comment")
            .Add(New classProperty("priceEnter", False, "DOUBLE NOT NULL"), "priceEnter")
            .Add(New classProperty("priceClose", False, "DOUBLE NOT NULL"), "priceClose")
            .Add(New classProperty("Status", False, "TEXT NOT NULL"), "Status")
            .Add(New classProperty("picOpenSize", False, "INT NOT NULL"), "picOpenSize")
            .Add(New classProperty("picCloseSize", False, "INT NOT NULL"), "picCloseSize")
            .Add(New classProperty("updated", False, "DATETIME NOT NULL"), "updated")
            .Add(New classProperty("lotSize", False, "DOUBLE NOT NULL"), "lotSize") ''picCloseHeight
            .Add(New classProperty("System", False, "TEXT NOT NULL"), "System")
            .Add(New classProperty("picCloseHeight", False, "INT NOT NULL"), "picCloseHeight")
            .Add(New classProperty("picCloseWidth", False, "INT NOT NULL"), "picCloseWidth")
            .Add(New classProperty("picOpenHeight", False, "INT NOT NULL"), "picOpenHeight")
            .Add(New classProperty("picOpenWidth", False, "INT NOT NULL"), "picOpenWidth")
        End With
    End Sub
End Class
